﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text; 
using System.Data;
using System.Data.OleDb;
using System.Web.UI;
using System.Web;
using System.Web.UI.WebControls;

namespace Util
{
    public class OleDbClass
    { 

        public OleDbClass()
        {
            
        }

        public static OleDbTransaction BeginOleDbTransaction()
        {
            OleDbConnection OleDbCon = new OleDbConnection(Util.OleDbHelper.ConnectionString);
            if (OleDbCon.State == ConnectionState.Closed)
                OleDbCon.Open();
            return OleDbCon.BeginTransaction();
        }
        /// <summary>
        /// 获取Excel档案数据到DataSet中
        /// </summary>
        /// <param name="ExcelPath">Excel文件所在路径</param>
        /// <param name="SheetName">工作薄名称</param>
        /// <returns></returns>
        public static Result GetExcelData(string ExcelPath,string SheetName,string strOrderBy)
        {
            string strOleDb = "";

            if (string.IsNullOrEmpty(strOrderBy))
            {
                strOrderBy = "";
            }

            strOleDb = "select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;IMEX=1;DATABASE=" + ExcelPath + "',' select * from [" + SheetName + "$] " + strOrderBy + "') ";

            

            return GetDataSet(strOleDb);
        }
        /// <summary>
        /// 执行OleDb语句返回DataSet
        /// </summary>
        /// <param name="strOleDb">OleDb语句,一定要防OleDb注入攻击</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static Result GetDataSet(string strOleDb)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            if (OleDbCon.State == System.Data.ConnectionState.Closed)
            {
				OleDbCon.Open();
            }
            Result rs = new Result();

            try
            {
                DataSet ds = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb);

                rs.IsSuccess = true;

                rs.DataResult = ds;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }

				OleDbCon.Dispose();
                
            }
            return rs;

        }
        /// <summary>
        /// 执行OleDb以填充Result中的DataSet
        /// </summary>
        /// <param name="strOleDb">数据库OleDb</param>
        /// <param name="rs">需要填充的结果集</param>
        /// <returns></returns>
        public static bool GetDataSet(string strOleDb,Result rs)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);
            try
            {
                if (OleDbCon.State == System.Data.ConnectionState.Closed)
                {
                    OleDbCon.Open();
                }
                rs.DataResult = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb);

                rs.IsSuccess = true;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;

                rs.IsSuccess = false;

                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
            return rs.IsSuccess;

        }
        /// <summary>
        /// 执行OleDb语句返回DataSet
        /// </summary>
        /// <param name="strOleDb">OleDb语句,一定要防OleDb注入攻击</param>
        /// <param name="m_OleDbParameters">OleDb参数数组</param>
        /// <returns></returns>
        public static Result GetDataSet(string strOleDb,OleDbParameter[] m_OleDbParameters)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            if (OleDbCon.State == System.Data.ConnectionState.Closed)
            {
                OleDbCon.Open();
            }
            Result rs = new Result();
            try
            {
                DataSet ds = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb, m_OleDbParameters);

                rs.IsSuccess = true;

                rs.DataResult = ds;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
            return rs;

        }
        /// <summary>
        /// 根据查询的关键字获取到数据列表
        /// </summary>
        /// <param name="strTableName">查询的表名</param>
        /// <param name="strKey">要查询的关键字</param>
        /// <param name="m_ParList">要查询的列名和值（值如果为空串则使用strKey作为查询条件）</param>
        /// <param name="m_OleDbTypes">要作为查询字段的OleDb类型数组</param>
        /// <param name="strOpreate">查询的操作符，如=，like,>= 等比较符号</param>
        /// <returns></returns>
        public static Result GetDataListBySearch(string strSelectOleDb,string strKey,Dictionary<string, object> m_ParList, System.Data.OleDb.OleDbType[] m_OleDbTypes,string[] strOpreate)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            Result rs = new Result();
            
            string strOleDb = strSelectOleDb;
            try
            {
                if (OleDbCon.State == System.Data.ConnectionState.Closed)
                {
                    OleDbCon.Open();
                }
                if (m_ParList != null)
                {
                    OleDbParameter[] m_OleDbParameters = new OleDbParameter[m_ParList.Count];

                    int i = 0;

                    foreach (KeyValuePair<string, object> m_Entry in m_ParList)
                    {
                        m_OleDbParameters[i] = new OleDbParameter("@" + m_Entry.Key, m_OleDbTypes[i]);

                        if (m_Entry.Value.ToString() == "")
                        {
                            m_OleDbParameters[i].Value = strKey;
                        }
                        else
                        {
                            m_OleDbParameters[i].Value = m_Entry.Value;
                        }
                        if (strOpreate[i].Trim().ToLower() == "like")
                        {
                            if (i == 0)
                            {
                                strOleDb += " Where " + m_Entry.Key + " " + strOpreate[i] + " '%'+" + m_OleDbParameters[i].ParameterName + "+'%'";
                            }
                            else
                            {
                                strOleDb += " or " + m_Entry.Key + " " + strOpreate[i] + " '%'+" + m_OleDbParameters[i].ParameterName + "+'%'";
                            }
                        }
                        else if (m_OleDbTypes[i] == OleDbType.Date)
                        {
                            if (i == 0)
                            {
                                strOleDb += " Where " + m_Entry.Key + strOpreate[i] + m_OleDbParameters[i].ParameterName;
                            }
                            else
                            {
                                strOleDb += " or " + m_Entry.Key + strOpreate[i] + m_OleDbParameters[i].ParameterName;
                            }
                        }
                        i++;
                    }
                    DataSet ds = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb, m_OleDbParameters);

                    rs.IsSuccess = true;

                    rs.DataResult = ds;
                }
                else
                {
                    DataSet ds = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb);

                    rs.IsSuccess = true;

                    rs.DataResult = ds;
                }
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
            return rs;
        }
        public static Result GetDataSet(OleDbTransaction tran,string strOleDb)
        {
            //OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);
            //if (OleDbCon.State == System.Data.ConnectionState.Closed)
            //{
            //    OleDbCon.Open();
            //}
            Result rs = new Result();
            try
            {                
                 DataSet ds = OleDbHelper.ExecuteDataset(tran.Connection, CommandType.Text, strOleDb);
                rs.IsSuccess = true;
                rs.DataResult = ds;
            }
            catch (Exception ex)
            {
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
                throw ex;
            }
            finally
            {
                //if (OleDbCon.State == System.Data.ConnectionState.Open)
                //{
                //    OleDbCon.Close();
                //}
                //OleDbCon.Dispose();
            }
            return rs;

        }

         
        /// <summary>
        /// 执行OleDb语句返回影响行数,如添加删除等
        /// </summary>
        /// <param name="strOleDb">OleDb语句,一定要防OleDb注入攻击</param>
        /// <param name="effect">输出行数</param>
        /// <returns></returns>
        public static  Result  GetEffects(string strOleDb)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            if (OleDbCon.State == ConnectionState.Closed)
            {
                OleDbCon.Open();
            }
            OleDbTransaction trans = OleDbCon.BeginTransaction();

            Result rs = new Result();
            try
            {
                int effect = OleDbHelper.ExecuteNonQuery(trans, CommandType.Text, strOleDb);

                if (effect > 0)
                {
                    rs.IsSuccess = true;
                    rs.Msg = "影响行数:"+effect .ToString ();
                    trans.Commit();
                }
                else
                {
                    rs.IsSuccess = false;
                    rs.Msg = "影响0行";
                }
            }
            catch (Exception ex)
            {
                trans.Rollback();
                rs.Msg = ex.Message;
                rs.IsSuccess = false;
            }
            finally
            {
                if (OleDbCon.State == ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();

				trans.Dispose();
            }
            return rs;
        }
        /// <summary>
        /// 执行OleDb语句返回OleDbDataReader
        /// </summary>
        /// <param name="strOleDb">OleDb语句,一定要防OleDb注入攻击</param>
        /// <returns></returns>
        public static  OleDbDataReader GetDataReader(string strOleDb)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            if (OleDbCon.State == ConnectionState.Closed)
            {
                OleDbCon.Open();
            }
            try
            {
                return OleDbHelper.ExecuteReader(OleDbCon, CommandType.Text, strOleDb);
            }
            catch
            {
                return null;
            }
            finally
            {
                if (OleDbCon.State == ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
        }
        /// <summary>
        /// 执行OleDb语句返回object 
        /// </summary>
        /// <param name="strOleDb">OleDb语句,一定要防OleDb注入攻击</param>
        /// <returns></returns>
        public static  object GetObject(string strOleDb)
        {
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            if (OleDbCon.State == ConnectionState.Closed)
            {
                OleDbCon.Open();
            } 
            try
            {
                return OleDbHelper.ExecuteScalar(OleDbCon, CommandType.Text, strOleDb);
            }
            catch
            {
                return null;
            }
            finally
            {
                if (OleDbCon.State == ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
        }
       public static Result GetModuleByUserID(int UserId)
        {
            Result rs = new Result();
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);
           string strOleDb = "";
           if (UserId != -1)
           {
               strOleDb = "select distinct ModuleCategory.* from dbo.ModuleCategory inner join dbo.RoleModule on ModuleCategory.id = dbo.RoleModule.ModuleId "
                       + "inner join dbo.RoleUser on dbo.RoleUser.RoleId = dbo.RoleModule.RoleId where   RoleUser.UserId = " + UserId.ToString() + " order by ModuleCategory.parentid,OrderNum asc";
              
           }
           else
           {
               strOleDb = "select ModuleCategory.* from dbo.ModuleCategory order by OrderNum asc";
           }
            if (OleDbCon.State == ConnectionState.Closed)
            {
                OleDbCon.Open();
            } 
           try
            {
                rs.DataResult = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb);
            }
            catch (Exception ex)
            {
                rs.IsSuccess = false;
                rs.Msg = ex.Message;
            }
           finally
           {
               if (OleDbCon.State == ConnectionState.Open)
               {
                   OleDbCon.Close();
               }
               OleDbCon.Dispose();
           }
            return rs;
        }
        
        /// <summary>
        /// 通过用户ID及模块ID返回菜单列表
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="modelID"></param>
        /// <returns></returns>
       public static string GetMenuList1(int userid, int modelID)
       {
           string menulist = "";
           int subcount = 0;           
           Result rs = new Result();
           rs = OleDbClass.GetModuleByUserID(userid);

           DataSet ds = new DataSet();
           ds = rs.DataResult;
           DataView dv = ds.Tables[0].DefaultView;
           for (int i = 0; i < dv.Count; i++)
           {
               int pid = Convert.ToInt32(dv[i]["ParentId"]);
               if (pid == modelID)
               {
                   subcount++;
                   int id = Convert.ToInt32(dv[i]["id"]);
                   //一级目录
                   menulist += "<tr>" +
                               "  <td id=\"td" + subcount.ToString() + "\" class=\"menuClose\"   onclick=\"HideMenu('td" + subcount.ToString() + "','tr" + subcount.ToString() + "')\">" +
                               "      <div class=\"menuTitleImg\"><img src=\"" + dv[i]["ImageUrl"].ToString() + "\" /></div> " +
                               "      <div class=\"menuTitle\">" + dv[i]["Name"].ToString() + " </div> " +
                               "   </td>    " +
                               "</tr>\r\n";

                   menulist += "<tr id=\"tr" + subcount.ToString() + "\" style=\"display:none\"> <td> ";
                   //下面找ParentId为上面ID的结点
                   for (int j = 0; j < dv.Count; j++)
                   {
                       int subPid = Convert.ToInt32(dv[j]["ParentId"]);
                       if (subPid == id)
                       { 
                           menulist += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
                       }
                   }
                   menulist += "</td></tr>	\r\n";
               }

           }
           return menulist;
       }
       /// <summary>
       /// 通过用户ID及模块ID返回菜单列表
       /// </summary>
       /// <param name="userid">用户ID</param>
       /// <param name="modelID">模块ID</param>
       /// <returns></returns>
       public static string GetMenuList(int userid, int modelID)
       {
           DataSet dsall = GetList(modelID).DataResult;
           DataSet dsModel = GetModuleIDs(userid).DataResult;
           DataView dv = dsall.Tables[0].DefaultView;
           DataView dvIds = dsModel.Tables[0].DefaultView;
           string menulist = "";
           int subcount = 0;
         
           int parentid = -1;
           Hashtable ht = new Hashtable();//是否已存放了一级
           for (int i = 0; i < dv.Count; i++)
           {  
               parentid = Convert.ToInt16(dv[i]["parentID"]);
               if (!ht.Contains(parentid.ToString()))
               {
                   string imageurl = dv[i]["ImageUrl"].ToString();
                   if (imageurl == "")
                   {
                       imageurl = "00";
                   }
                   ht.Add(parentid.ToString(), parentid.ToString());
                   subcount++;                
                   //一级目录
                   menulist += "<tr>" +
                               "  <td id=\"td" + subcount.ToString() + "\" class=\"menuClose\"   onclick=\"HideMenu('td" + subcount.ToString() + "','tr" + subcount.ToString() + "')\">" +
                               "      <div class=\"menuTitleImg\"><img src=\"" + dv[i]["modelImage"].ToString() + "\" /></div> " +
                               "      <div class=\"menuTitle\">" + dv[i]["subModelName"].ToString() + " </div> " +
                               "   </td>    " +
                               "</tr>\r\n";
                   menulist += "<tr id=\"tr" + subcount.ToString() + "\" style=\"display:none\"> <td> ";
                   //下面找ParentId为上面ID的结点
                   if (userid == 1)
                   {
                       menulist += GetMenu(dv, parentid, dvIds,1);
                   }
                   else
                   {
                       menulist += GetMenu(dv, parentid, dvIds);
                   }
                   menulist += "</td></tr>	\r\n";               
               }   
           }
           //系统菜单
           string systemMenu = "<tr>  <td id=\"tdSystemMenu\" class=\"menuClose\"   onclick=\"HideMenu('tdSystemMenu','trSystemMenu')\">      <div class=\"menuTitleImg\"><img src=\"../images/menuLogo/18.gif\" /></div>       <div class=\"menuTitle\">系统设置 </div></td></tr><tr id=\"trSystemMenu\" style=\"display:none\"> <td> <div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"../usermanage/userpassword.aspx\" > 修改密码</a></div><div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"../usermanage/userMyinfo.aspx\" > 个人信息管理</a></div>	</td></tr>";
           menulist += systemMenu;
           return menulist;
       } 
       public static string GetMenu(DataView dv, int parentid, DataView dvids)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               {
                   bool b = false;
                   for (int i = 0; i < dvids.Count; i++)
                   {
                       if (dvids[i]["ModuleId"].ToString() == dv[j]["id"].ToString())
                       {
                           b = true;
                           break;
                       }
                   }
                   if (b)
                   {
                       list += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
                   }
                   else
                   {
                       string IsShowControl = "";

                       if (Util.DataConvert.GetInt32Value(System.Configuration.ConfigurationManager.AppSettings["IsMenuItemShow"]) == -1)
                       {
                           IsShowControl = " style='display:none'";
                       }
                       list += "<div class=\"menuItemFalse\" " + IsShowControl + ">·  " + dv[j]["Name"].ToString() + " </div>	\r\n";
                   }
               }
           }
           return list;
       }
    
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dv"></param>
        /// <param name="parentid"></param>
        /// <param name="dvids"></param>
        /// <param name="power">是否所有权限</param>
        /// <returns></returns>
        public static string GetMenu(DataView dv, int parentid, DataView dvids,int power)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               { 
                   list += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
                }
           }
           return list;
       }
       /// <summary>
       /// 开发商菜单
       /// </summary> 
       /// <param name="modelID">模块ID</param>
       /// <returns></returns>
       public static string GetCompanyMenuList(int modelID)
       {
           DataSet dsall = GetList(modelID).DataResult; 
           DataView dv = dsall.Tables[0].DefaultView; 
           string menulist = "";
           int subcount = 0; 
           int parentid = -1;
           Hashtable ht = new Hashtable();//是否已存放了一级
           for (int i = 0; i < dv.Count; i++)
           {
               parentid = Convert.ToInt16(dv[i]["parentID"]);
               if (!ht.Contains(parentid.ToString()))
               {
                   string imageurl = dv[i]["ImageUrl"].ToString();
                   if (imageurl == "")
                   {
                       imageurl = "00";
                   }
                   ht.Add(parentid.ToString(), parentid.ToString());
                   subcount++;
                   //一级目录
                   menulist += "<tr>" +
                               "  <td id=\"td" + subcount.ToString() + "\" class=\"menuClose\"   onclick=\"HideMenu('td" + subcount.ToString() + "','tr" + subcount.ToString() + "')\">" +
                               "      <div class=\"menuTitleImg\"><img src=\"" + dv[i]["modelImage"].ToString() + "\" /></div> " +
                               "      <div class=\"menuTitle\">" + dv[i]["subModelName"].ToString() + " </div> " +
                               "   </td>    " +
                               "</tr>\r\n";
                   menulist += "<tr id=\"tr" + subcount.ToString() + "\" style=\"display:none\"> <td> ";
                   //下面找ParentId为上面ID的结点
                   menulist += GetCompanyMenu(dv, parentid);
                   menulist += "</td></tr>	\r\n";
               }

           }
           return menulist;
       }

        //
        /// <summary>
       /// 开发商菜单列表
        /// </summary>
        /// <param name="dv"></param>
        /// <param name="parentid"></param>
        /// <returns></returns>
       public static string GetCompanyMenu(DataView dv, int parentid)
       {
           string list = "";
           for (int j = 0; j < dv.Count; j++)
           {
               int subPid = Convert.ToInt32(dv[j]["ParentId"]);
               if (subPid == parentid)
               {
                   list += "<div class=\"menuItem\">· <a target=\"mainFrame\"  href=\"" + dv[j]["url"].ToString() + "\" > " + dv[j]["Name"].ToString() + "</a></div>	\r\n";
               }
           }
           return list;
       }

        /// <summary>
        /// 根据大模块ID返回所有功能列表
        /// </summary>
        /// <param name="modelID"></param>
        /// <returns></returns>
       public static Result GetList(int modelID)
       {
           string OleDbstr = " SELECT ModuleCategory.* , SubModel.imageurl as modelImage, SubModel.Name AS subModelName,SubModel.id as submodelID,submodel.ordernum as subOrderNum "
                            + " FROM ModuleCategory INNER JOIN "
                             + " ModuleCategory AS SubModel ON ModuleCategory.ParentId = SubModel.Id INNER JOIN "
                             + " ModuleCategory AS Model ON SubModel.ParentId = Model.Id where model.id=" + modelID.ToString() + "  and ModuleCategory.state>-1 order by SubModel.ordernum,ModuleCategory.ordernum";
           return OleDbClass.GetDataSet(OleDbstr);       
       }

        /// <summary>
        /// 根据用户ID返回功能的ID列表
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
       public static Result GetModuleIDs(int userID)
       {
           string OleDbstr = "  SELECT distinct RoleModule.ModuleId  " +
                            "    FROM RoleUser INNER JOIN " +
                            "   RoleModule ON RoleUser.RoleId = RoleModule.RoleId INNER JOIN " +
                            "  ModuleCategory ON RoleModule.ModuleId = ModuleCategory.Id  " +
                            "  where userid=" + userID.ToString() +
                       " union select ModuleCategory.Id  from UserPower INNER JOIN   ModuleCategory ON UserPower.PowerId = ModuleCategory.Id " +
                                " where userId=" + userID.ToString();
           return OleDbClass.GetDataSet(OleDbstr);
       }

 


      
        /// <summary>
        /// 返回在线用户数
        /// </summary>
        /// <returns></returns>
       public static int OnlineUsers()
       {
           return 0;
       }

        public static void AddTree(int ParentID, TreeNode pNode,DataSet ds,TreeView treeView)
        {
            DataRow[] drs = ds.Tables[0].Select("ParentID=" + ParentID);
            foreach (DataRow dr in drs)
            {
                TreeNode Node = new TreeNode();
                if (pNode == null)                  
                {
                    Node.Value = dr["Id"].ToString(); 
                    Node.Text = dr["Name"].ToString();
                    treeView.Nodes.Add(Node);
                    AddTree(Convert.ToInt32(dr["Id"]), Node,ds,treeView); //递归
                }
                else //处理子节点
                {
                    Node.Value = dr["Id"].ToString();
                    Node.Text = dr["Name"].ToString();
                    pNode.ChildNodes.Add(Node);
                    AddTree(Convert.ToInt32(dr["Id"]), Node, ds, treeView); //递归
                }
            }
        }
        /// <summary>
        /// 返回公告信息
        /// </summary>
        /// <returns></returns>
        public static string GetAnnounce()
        {
            return "公告信息：欢迎使用房产信息管理系统";
        }
        /// <summary>
        /// 返回待办事项数
        /// </summary>
        /// <param name="uid">用户ID</param>
        /// <returns></returns>
        public static string GetWorks(int uid)
        {
            return "0";
        }
        public static string GetMessages(int uid)
        {
            return "<items>" +
                        "<item><name>新消息1</name></item>" +
                        "<item><name>新消息2</name></item>" +
                        "<item><name>新消息3</name></item>" +
                        "<item><name>新消息4</name></item>" +
                    "</items>";
        }
        /// <summary>
        /// 返回基础数据分类
        /// </summary>
        /// <param name="categoryName"></param>
        /// <returns></returns>
        public static Result GetBasicCategory(string categoryName)
        {
            string OleDbstr = "select * from BasicCategory where  categorytype='" + categoryName.Replace("'", "''").Trim() + "'";
            return OleDbClass.GetDataSet(OleDbstr);

        }
        public static string GetBasicCategory(int id)
        {
            string OleDbstr = "select top 1 CategoryName  from BasicCategory where  id=" + id.ToString();
            return OleDbClass.GetObject(OleDbstr).ToString();

        }     
       

                /// <summary>
        /// 根据查询条件获取所有相关联业务信息
        /// </summary>
        /// <param name="strMappingNo">测绘编号</param>
        /// <param name="strOperationNumber">业务宗号</param>
        /// <param name="strPropertyRight">产权人</param>
        /// <param name="strPRNo_Display">产权证号</param>
        /// <param name="strCertificateNo">身份证号</param>
        /// <param name="strReposeName">房屋坐落地址</param>
        /// <returns></returns>
        /// ,RegisteDate0,ReadDate0
        public static Result GetRelevancyBussiness(string strMappingNo, string strOperationNumber, string strPropertyRight, string strPRNo_Display, string strCertificateNo, string strReposeName,string RegisterDate,string ReadDate,string RegisteDate0,string ReadDate0,int iCurrentPage, int iPageSize)
        {
            string strOleDb = "";
            Result rs = new Result();
            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);
            try
            {
                int iRecordCount = Convert.ToInt32(OleDbHelper.ExecuteScalar(OleDbCon, CommandType.Text, "select count(*) from HouseRegInfo left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id"));
                rs.pageInfo= new PageInfo(iCurrentPage, iPageSize, iRecordCount);
                int nextpage = iPageSize * (iCurrentPage - 1);
              
                if (iCurrentPage > 0)
                {
                    string strWhere = " where MappingNo like '%" + strMappingNo.Replace("'", "") + "%' and OperationNumber like '%" + strOperationNumber.Replace("'", "") + "%' and PropertyRight like '%" + strPropertyRight.Replace("'", "") + "%'  and PRNo_Display like '%" + strPRNo_Display.Replace("'", "") + "%'  and CertificateNo like '%" + strCertificateNo.Replace("'", "") + "%' and ReposeName like '%" + strReposeName.Replace("'", "") +"%'";

                    if (RegisterDate != "")
                    {
                        strWhere +=" and HouseRegInfo.CreateTime>Convert(datetime,'" + RegisterDate + "')";
                    }
                    if(ReadDate!="")
                    {
                        strWhere += " and HouseRegInfo.CreateTime<Convert(datetime,'" + ReadDate + "')";
                    }
                    if(RegisteDate0!="")
                    {
                        strWhere+= " and HouseRegInfo.PullDownCertTime>Convert(datetime,'" + RegisteDate0 + "')";
                    }
                    if(ReadDate0!="")
                    {
                        strWhere+=" and  HouseRegInfo.PullDownCertTime<Convert(datetime,'" + RegisteDate0 + "')";
                    }
                    strOleDb = "select top " + iPageSize + " HouseRegInfo.*,RegistrationType.Name as BussinessTypeName from HouseRegInfo ";

                    strOleDb += " left join RegistrationType on HouseRegInfo.BussinessTypeId=RegistrationType.Id ";

                    strOleDb += strWhere;

                    strOleDb += " and HouseRegInfo.Id not in";

                    strOleDb += " (select top " + nextpage + " HouseRegInfo.id from HouseRegInfo ";

                    strOleDb += strWhere + " order by HouseRegInfo.Id asc)";

                    strOleDb += " order by HouseRegInfo.Id asc";

                }
                else
                {
                    
                }
                rs.DataResult = OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb);

 
                return rs;

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
        }

        public static int GetTableCount(string strOleDb)
        {       
            int iRecordCount=-1;

            OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            OleDbCon.Open();

            try
            {                
                iRecordCount = Convert.ToInt32(OleDbHelper.ExecuteScalar(OleDbCon, CommandType.Text, strOleDb));               
            }
            catch (Exception ex)
            { 
                iRecordCount=-1;

                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
            return iRecordCount;
        }

        public static DataSet ExecuteDataset(string strOleDb) 
        {
			OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            DataSet ds = new DataSet();
            try
            {
                OleDbCon.Open();
                
                ds=OleDbHelper.ExecuteDataset(OleDbCon, CommandType.Text, strOleDb);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
                OleDbCon.Dispose();
            }
            return ds;
        }

		//private static OleDbConnection GetOleDbCon()
		//{
		//    OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);
		//    return OleDbCon;
		//}
        /// <summary>
        /// 更新表中的记录公用方法
        /// </summary>
        /// <param name="OleDbTran">数据库事务</param>
        /// <param name="strTableName">表名</param>
        /// <param name="strSetOleDb">设置的OleDb值</param>
        /// <param name="strWhere">Where条件</param>
        /// <returns></returns>
        public static Result UpdateTable(OleDbTransaction  OleDbTran,string strTableName,string strSetOleDb,string strWhere)
        {
            Result rs = new Result();

            string strOleDb = "Update " + strTableName + " " + strSetOleDb + " " + strWhere;

            try
            {
                int i = OleDbHelper.ExecuteNonQuery(OleDbTran, CommandType.Text, strOleDb);

                if (i > 0)
                {
                    rs.IsSuccess = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            
        }
        /// <summary>
        /// 更新表中的记录公用方法
        /// </summary>
        /// <param name="OleDbTran">数据库事务</param>
        /// <param name="strTableName">表名</param>
        /// <param name="strWhere">Where条件</param>
        /// <param name="m_OleDbParameters">OleDb参数和值对象数组</param>
        /// <returns></returns>
        public static Result UpdateTable(OleDbTransaction OleDbTran, string strTableName, string strWhere, OleDbParameter[] m_OleDbParameters)
        {
            Result rs = new Result();

            if (m_OleDbParameters == null)
            {
                throw new Exception("OleDbParameter[]参数不能为空对象！");
            }
            string[] strColumns = new string[m_OleDbParameters.GetLength(0)];

            string strOleDb = "Update " + strTableName + " Set ";

            for (int i = 0; i < m_OleDbParameters.GetLength(0); i++)
            {
                strColumns[i] = m_OleDbParameters[i].ParameterName.Substring(1);

                strOleDb += strColumns[i] + "=" + m_OleDbParameters[i].ParameterName + ",";

                if (i == m_OleDbParameters.GetLength(0) - 1)
                {
                    strOleDb += strColumns[i] + "=" + m_OleDbParameters[i].ParameterName + ",";
                }
                else
                {
                    strOleDb += strColumns[i] + "=" + m_OleDbParameters[i].ParameterName;
                }
            }

             strOleDb += " " + strWhere;

            try
            {
                int i = OleDbHelper.ExecuteNonQuery(OleDbTran, CommandType.Text, strOleDb,m_OleDbParameters);

                if (i > 0)
                {
                    rs.IsSuccess = true;
                }
                return rs;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
		/// <summary>
		/// 执行OleDb语句以获取DataTable对象
		/// </summary>
		/// <param name="strOleDb">OleDb语句字符串</param>
		/// <returns></returns>
        public static DataTable ExecuteDataTable(string strOleDb)
        {
			OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

            DataTable dt = new DataTable();
            try
            {
                OleDbCon.Open();

				dt = OleDbHelper.ExecuteDataTable(OleDbCon, CommandType.Text, strOleDb);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (OleDbCon.State == System.Data.ConnectionState.Open)
                {
                    OleDbCon.Close();
                }
				OleDbCon.Dispose();
            }
            return dt;
 
        }
		/// <summary>
		/// 执行分页存储过程方法
		/// </summary>
		/// <param name="strTableName">要查询的表名称和连接的表</param>
		/// <param name="strWhere">Where查询条件</param>
		/// <param name="strSelectColumns">需要选择查询的列名</param>
		/// <param name="CurrentPageIndex">当前页码</param>
		/// <param name="PageSize">每页的记录数大小</param>
		/// <param name="strOrderBy">Order By 排序语句</param>
		/// <param name="PKName">主查询表的主键名称</param>
		/// <param name="TotalCount">记录总数</param>
		/// <returns></returns>
		public static Result ExecPagenationSP(string strTableName,string strWhere,string strSelectColumns,int iCurrentPage,int iPageSize,string strOrderBy,string strPKName)
		{
			Result rs = new Result();

			OleDbConnection OleDbCon = new OleDbConnection(OleDbHelper.ConnectionString);

			OleDbCon.Open();

			try
			{
				OleDbParameter[] m_OleDbParameters = new OleDbParameter[8];

				if (!string.IsNullOrEmpty(strTableName))
				{
					m_OleDbParameters[0] = new OleDbParameter("@strTableName", strTableName);
				}
				else
				{
					m_OleDbParameters[0] = new OleDbParameter("@strTableName",null);
				}
				if (!string.IsNullOrEmpty(strWhere))
				{
					m_OleDbParameters[1] = new OleDbParameter("@strWhere", strWhere);
				}
				else
				{
					m_OleDbParameters[1] = new OleDbParameter("@strWhere",null);
				}
				if (!string.IsNullOrEmpty(strSelectColumns))
				{
					m_OleDbParameters[2] = new OleDbParameter("@strSelectColumns", strSelectColumns);
				}
				else
				{
					m_OleDbParameters[2] = new OleDbParameter("@strSelectColumns",null);
				}
				m_OleDbParameters[3] = new OleDbParameter("@currentPage",iCurrentPage);

				m_OleDbParameters[4] = new OleDbParameter("@pageSize",iPageSize);

				if (!string.IsNullOrEmpty(strOrderBy))
				{
					m_OleDbParameters[5] = new OleDbParameter("@orderby", strOrderBy);
				}
				else
				{
					m_OleDbParameters[5] = new OleDbParameter("@orderby",null);
				}
				if (!string.IsNullOrEmpty(strPKName))
				{
					m_OleDbParameters[6] = new OleDbParameter("@PKName", strPKName);
				}
				else
				{
					m_OleDbParameters[6] = new OleDbParameter("@PKName",null);
				}
				m_OleDbParameters[7] = new OleDbParameter("@total",0);

				m_OleDbParameters[7].Direction = ParameterDirection.Output;

				rs.DataResult = OleDbHelper.ExecuteDataset(OleDbCon,CommandType.StoredProcedure, "sp_Pagination",m_OleDbParameters);

				int iTotal = DataConvert.GetInt32Value(m_OleDbParameters[7].Value);

				rs.pageInfo = new PageInfo(iCurrentPage, iPageSize, iTotal);

				rs.IsSuccess = true;
			}
			catch (Exception ex)
			{
				rs.IsSuccess = false;
				rs.Msg = ex.Message;
				throw ex;
			}
			finally
			{
				if (OleDbCon.State == System.Data.ConnectionState.Open)
				{
					OleDbCon.Close();
				}
				OleDbCon.Dispose();
			}
			return rs;
		}
    }
}
